﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO

Public Class LichTuyenKhongLapDao

    Dim provider As New DataProvider

    Public Function LayDanhSachLichTuyenKhongLap() As DataTable
        Dim sql As String = "SELECT MaTuyen,NgayKhoiHanh FROM LichTuyenKhongLap"
        Return provider.ThucThiCauTruyVan(sql)
    End Function
    Public Function LayDanhSachLichTuyenKhongLapThoiDiem() As DataTable
        Dim sql As String = "SELECT MaTuyen,NgayKhoiHanh,ThoiDiemKhoiHanh FROM LichTuyenKhongLap"
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function ThemThoiDiemKhoiHanh(ByVal thoiDiem As LichTuyenKhongLapDto) As Integer
        Dim sql As String = "INSERT INTO LichTuyenKhongLap(MaTuyen,ThoiDiemKhoiHanh,NgayKhoiHanh,MaNhanVien) VALUES(?,?,?,?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", thoiDiem.MaTuyen))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", thoiDiem.ThoiDiemKhoiHanh))
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", thoiDiem.NgayKhoiHanh))
        dsParameter.Add(New OleDbParameter("@MaNhanVien", thoiDiem.MaNhanVien))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function CapNhatThoiDiemKhoiHanh(ByRef data As DataTable) As Integer

        Dim dataAdapter As New OleDbDataAdapter()
        Dim con As OleDbConnection = provider.Connect()

        Dim sqlInsert As String = "INSERT INTO LichTuyenKhongLap (MaTuyen,NgayKhoiHanh,ThoiDiemKhoiHanh) VALUES(?,?,?)"
        Dim commandInsert As New OleDbCommand(sqlInsert, con)
        commandInsert.Parameters.Add(New OleDbParameter("@MaTuyen", OleDbType.Integer, 4, "MaTuyen"))
        commandInsert.Parameters.Add(New OleDbParameter("@NgayKhoiHanh", OleDbType.Date, 10, "NgayKhoiHanh"))
        commandInsert.Parameters.Add(New OleDbParameter("@ThoiDiemKhoiHanh", OleDbType.VarChar, 10, "ThoiDiemKhoiHanh"))
        dataAdapter.InsertCommand = commandInsert

        Dim sqlDelete As String = "Delete From LichTuyenKhongLap Where MaTuyen=? And NgayKhoiHanh=? and ThoiDiemKhoiHanh=? "
        Dim commandDelete As New OleDbCommand(sqlDelete, con)
        commandDelete.Parameters.Add(New OleDbParameter("@MaTuyen", OleDbType.Integer, 4, "MaTuyen"))
        commandDelete.Parameters.Add(New OleDbParameter("@NgayKhoiHanh", OleDbType.Date, 10, "NgayKhoiHanh"))
        commandDelete.Parameters.Add(New OleDbParameter("@ThoiDiemKhoiHanh", OleDbType.VarChar, 10, "ThoiDiemKhoiHanh"))
        dataAdapter.DeleteCommand = commandDelete

        Return dataAdapter.Update(data)

    End Function
End Class

